====================================
SCHEMA :
ENTITY :
ATTRIBUTE :
TUPLES :
====================================

DDL (Data Definition Language)
CREATE , ALTER, RENAME, TRUNCATE, DROP, MODIFY
------------------------------
DML (Data manipulation language)
SELECT , INSERT, UPDATE, DELETE
--------------------------------

DCL (Data Control Language)
GRANT, REVOKE
---------------------------
TCL (Transaction Control Language)
COMMIT, ROLLBACK, SAVEPOINT, BEGIN
----------------------------------
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\lenovo>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
7 rows in set (0.05 sec)

mysql> CREATE DATABASE `nb158h`;
Query OK, 1 row affected (0.03 sec)

mysql> USE `nb158h`;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE `users`(
    -> `id` INT AUTO_INCREMENT PRIMARY KEY,
    -> `name` VARCHAR(80),
    -> `email` VARCHAR(100),
    -> `password` VARCHAR(32),
    -> `status` ENUM('0','1'),
    -> `added_date` DATETIME,
    -> `modified_date` TIMESTAMP);
Query OK, 0 rows affected (0.14 sec)

mysql> DESC `users`;
+---------------+---------------+------+-----+-------------------+--------------
---------------+
| Field         | Type          | Null | Key | Default           | Extra
               |
+---------------+---------------+------+-----+-------------------+--------------
---------------+
| id            | int(11)       | NO   | PRI | NULL              | auto_incremen
t              |
| name          | varchar(80)   | YES  |     | NULL              |
               |
| email         | varchar(100)  | YES  |     | NULL              |
               |
| password      | varchar(32)   | YES  |     | NULL              |
               |
| status        | enum('0','1') | YES  |     | NULL              |
               |
| added_date    | datetime      | YES  |     | NULL              |
               |
| modified_date | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CUR
RENT_TIMESTAMP |
+---------------+---------------+------+-----+-------------------+--------------
---------------+
7 rows in set (0.05 sec)

mysql> INSERT INTO `users` VALUES
    -> (NULL,'Rasmus Lerdorf','zeev@zend.com','1q1q1q','1',NOW(),NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM `users`;
+----+----------------+---------------+----------+--------+---------------------
+---------------------+
| id | name           | email         | password | status | added_date
| modified_date       |
+----+----------------+---------------+----------+--------+---------------------
+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com | 1q1q1q   | 1      | 2013-12-06 09:03:16
| 2013-12-06 09:03:16 |
+----+----------------+---------------+----------+--------+---------------------
+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO `users`
    -> (`name`,`status`,`added_date`,`email`,`password`)
    -> VALUES
    -> ('vikas','0',NOW(),'vikas@gmail.com','1q1q1q');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM `users`;
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
| id | name           | email           | password | status | added_date
  | modified_date       |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:1
6 | 2013-12-06 09:03:16 |
|  2 | vikas          | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:3
5 | 2013-12-06 09:15:35 |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO `users` SET
    -> `name`='amit',
    -> `email`='abc@gmail.com',
    -> `password`='1q1q1q',
    -> `status`='1',
    -> `added_date`= NOW() ;
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM `users`;
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
| id | name           | email           | password | status | added_date
  | modified_date       |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:1
6 | 2013-12-06 09:03:16 |
|  2 | vikas          | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:3
5 | 2013-12-06 09:15:35 |
|  3 | amit           | abc@gmail.com   | 1q1q1q   | 1      | 2013-12-06 09:17:5
5 | 2013-12-06 09:17:55 |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
3 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `users` WHERE `id`='2';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM `users`;
+----+----------------+---------------+----------+--------+---------------------
+---------------------+
| id | name           | email         | password | status | added_date
| modified_date       |
+----+----------------+---------------+----------+--------+---------------------
+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com | 1q1q1q   | 1      | 2013-12-06 09:03:16
| 2013-12-06 09:03:16 |
|  3 | amit           | abc@gmail.com | 1q1q1q   | 1      | 2013-12-06 09:17:55
| 2013-12-06 09:17:55 |
+----+----------------+---------------+----------+--------+---------------------
+---------------------+
2 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM `users`;
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
| id | name           | email           | password | status | added_date
  | modified_date       |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:1
6 | 2013-12-06 09:03:16 |
|  2 | vikas          | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:3
5 | 2013-12-06 09:15:35 |
|  3 | amit           | abc@gmail.com   | 1q1q1q   | 1      | 2013-12-06 09:17:5
5 | 2013-12-06 09:17:55 |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
3 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM `users`;
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT * FROM `users`;
Empty set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM `users`;
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
| id | name           | email           | password | status | added_date
  | modified_date       |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:1
6 | 2013-12-06 09:03:16 |
|  2 | vikas          | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:3
5 | 2013-12-06 09:15:35 |
|  3 | amit           | abc@gmail.com   | 1q1q1q   | 1      | 2013-12-06 09:17:5
5 | 2013-12-06 09:17:55 |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
3 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `users` SET `name`='Hemant Sharma'
    -> WHERE `id`='3';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM `users`;
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
| id | name           | email           | password | status | added_date
  | modified_date       |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:1
6 | 2013-12-06 09:03:16 |
|  2 | vikas          | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:3
5 | 2013-12-06 09:15:35 |
|  3 | Hemant Sharma  | abc@gmail.com   | 1q1q1q   | 1      | 2013-12-06 09:17:5
5 | 2013-12-06 09:27:21 |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
3 rows in set (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE `users` SET `name`='Hemant Sharma';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> SELECT * FROM `users`;
+----+---------------+-----------------+----------+--------+--------------------
-+---------------------+
| id | name          | email           | password | status | added_date
 | modified_date       |
+----+---------------+-----------------+----------+--------+--------------------
-+---------------------+
|  1 | Hemant Sharma | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:16
 | 2013-12-06 09:27:54 |
|  2 | Hemant Sharma | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:35
 | 2013-12-06 09:27:54 |
|  3 | Hemant Sharma | abc@gmail.com   | 1q1q1q   | 1      | 2013-12-06 09:17:55
 | 2013-12-06 09:27:21 |
+----+---------------+-----------------+----------+--------+--------------------
-+---------------------+
3 rows in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.36 sec)

mysql> SELECT * FROM `users`;
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
| id | name           | email           | password | status | added_date
  | modified_date       |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
|  1 | Rasmus Lerdorf | zeev@zend.com   | 1q1q1q   | 1      | 2013-12-06 09:03:1
6 | 2013-12-06 09:03:16 |
|  2 | vikas          | vikas@gmail.com | 1q1q1q   | 0      | 2013-12-06 09:15:3
5 | 2013-12-06 09:15:35 |
|  3 | Hemant Sharma  | abc@gmail.com   | 1q1q1q   | 1      | 2013-12-06 09:17:5
5 | 2013-12-06 09:27:21 |
+----+----------------+-----------------+----------+--------+-------------------
--+---------------------+
3 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql>
